<?php
    class Client extends Personne{
        //Alias sur table : cl
        public $login;

        //Constructeur complet
        // (Civilite)civilite, (Profil)profil
        public function __construct($id_client, $civilite, $profil, $login, $mdp, $nom, $prenom, $telephone, $mobile, $email, 
                                    $date_naissance, $date_ajout, $date_modif, $valide) {
            $this->setId($id_client);
            $this->setCivilite($civilite);
            $this->setProfil($profil);
            $this->setLogin($login);
            $this->setMdp($mdp);
            $this->setPrenom($prenom);
            $this->setNom($nom);
            $this->setTelephone($telephone);
            $this->setMobile($mobile);
            $this->setEmail($email);
            $this->setDateNaissance($date_naissance);
            $this->setDateAjout($date_ajout);
            $this->setDateModif($date_modif);
            $this->setValide($valide);
        }
	
        //Retourne le prénom et le nom du produit
        //Paramètre : aucun
        //Retourne (String)
        public function __toString() {
            return $this->prenom . ' ' . $this->nom;
        }
        
        //Ajoute un objet Client à la BDD
        //Paramètre : aucun
        //Retourne (Client)
        public function add() {
            $utilise = Client::estDejaUtilise($this->login);
            if(!$utilise) {
                $sql =  'INSERT INTO client (id_civilite, id_profil, login, mdp, prenom, nom, telephone, mobile, 
                                            email, date_naissance, date_ajout, date_modif, valide) VALUES
                        ('.intval($this->civilite->getId()).', '.intval($this->profil->getId()).', 
                        "'.Db::insertion($this->login).'", "'.Db::insertion($this->mdp).'", "'.Db::insertion($this->prenom).'", 
                        "'.Db::insertion($this->nom).'", "'.Db::insertion($this->telephone).'", "'.Db::insertion($this->mobile).'", 
                        "'.Db::insertion($this->email).'", "'.$this->getDateNaissance().'", "'.$this->getDateAjout().'", 
                        "'.$this->getDateModif().'", '.intval($this->valide).')';
                $this->setId(Db::Execute($sql));
            }
            return $this;
        }
        
        //Met à jour l'objet Client dans la BDD
        //Paramètre : aucun
        //Retourne (Client)
        public function update() {
            $this->setDateModif(new DateTime());
            $sql =  'UPDATE client
                    SET id_civilite = '.intval($this->civilite->getId()).', 
                        prenom = "'.Db::insertion($this->prenom).'", 
                        nom = "'.Db::insertion($this->nom).'", 
                        telephone = "'.Db::insertion($this->telephone).'", 
                        mobile = "'.Db::insertion($this->mobile).'", 
                        email = "'.Db::insertion($this->email).'", 
                        date_naissance = "'.$this->getDateNaissance().'", 
                        date_modif = "'.$this->getDateModif().'", 
                        valide = '.intval($this->valide).', 
                        id_profil = '.intval($this->profil->getId()).'
                    WHERE
                        id_client = '.intval($this->getId());
            Db::Execute($sql);
            return $this;
        }
        
        //Change le mot de passe de ce Client
        //Paramètre : (aucun)
        //Retourne (Client)
        public function changementMdp() {
            $this->setDateModif(new DateTime());
            $sql =  'UPDATE client
                    SET mdp = "'.Tools::encrypt($this->mdp).'",
                        date_modif = "'.$this->getDateModif().'"
                    WHERE
                        id_client = '.intval($this->getId());
            Db::Execute($sql);
            return $this;
        }
        
        //Désactive ce client
        //Paramètre : aucun
        //Retourne (Client)
        public function suppr() {
            $this->setValide(false);
            $this->update();
        }
        
        //Récupère les commandes d'un client grâce à son ID
        //Paramètre : Aucun
        //Retourne collections de la classe Commande
        public function getCommandes(){
            $sql =  'SELECT
                        c.id_commande as cid_commande, c.prix_total_ht as cprix_total_ht, c.prix_total_ttc as cprix_total_ttc, 
                        c.frais_transporteur as cfrais_transporteur, c.numero_livraison as cnumero_livraison, c.date_livraison as cdate_livraison,
                        c.date_ajout as cdate_ajout, c.date_modif, 
                        a.id_adresse as aid_adresse, a.adr1 as aadr1, a.adr2 as aadr2, a.ville as aville, a.cp as acp, a.pays as apays, a.supprime as asupprime, 
                        a.valide as avalide, 
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide
                        p.id_panier as pid_panier, p.date_ajout as pdate_ajout, p;date_modif as pdate_modif, 
                        cl2.id_client as cl2id_client, cl2.login as cl2login, cl2.mdp as cl2mdp, cl2.prenom as cl2prenom, 
                        cl2.mdp as cl2mdp, cl2.telephone as cl2telephone, cl2.mobile as cl2mobile, 
                        cl2.email as cl2email, cl2.date_naissance as cl2date_naissance, cl2.date_ajout, cl2.date_modif, cl2.valide as cl2valide, 
                        pro2.id_profil as pro2id_profil, pro2.libelle as pro2libelle, pro2.valide as pro2valide,
                        civ2.id_civilite as civ2id_civilite, civ2.libelle as civ2libelle, civ2.diminutif as civ2diminutif, civ2.valide as civ2valide,
                        t.id_transporteur as tid_transporteur, t.libelle as tlibelle, t.email as temail, t.franco_port as tfranco_port, 
                        t.frais_port as tfrais_port, t.date_ajout as tdate_ajout, t.date_modif, t.valide as tvalide
                        tv.id_tva as tvid_tva, tv.taux as tvtaux,
                        mp.id_moyen_paiement as mpid_moyen_paiement, mp.libelle as mplibelle, mp.valide as mpvalide,
                        s.id_statut as sid_statut, s.libelle as slibelle, s.fin as sfin, s.valide as svalide,
                        av.id_avoir as avid_avoir, av.id_commande as avid_commande, av.montant as avmontant, avvalide as avvalide, 
                        cl3.id_client as cl3id_client, cl3.login as cl3login, cl3.mdp as cl3mdp, cl3.prenom as cl3prenom, 
                        cl3.mdp as cl3mdp, cl3.telephone as cl3telephone, cl3.mobile as cl3mobile, 
                        cl3.email as cl3email, cl3.date_naissance as cl3date_naissance, cl3.date_ajout, cl3.date_modif, cl3.valide as cl3valide, 
                        pro3.id_profil as pro3id_profil, pro3.libelle as pro3libelle, pro3.valide as pro3valide,
                        civ3.id_civilite as civ3id_civilite, civ3.libelle as civ3libelle, civ3.diminutif as civ3diminutif, civ3.valide as civ3valide		
                    FROM commande c
                    LEFT JOIN adresse a ON c.id_adresse = a.id_adresse
                    LEFT JOIN client cl ON a.id_client = cl.id_client 
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    LEFT JOIN panier p ON c.id_panier = p.id_panier
                    LEFT JOIN client cl2 ON p.id_client = cl2.id_client
                    LEFT JOIN profil pro2 ON cl2.id_profil = pro2.id_profil
                    LEFT JOIN civilite civ2 ON cl2.id_civilite = civ2.id_civilite
                    LEFT JOIN transporteur t ON c.id_transporteur = t.id_transporteur
                    LEFT JOIN tva tv ON t.id_tva = tv.id_tva
                    LEFT JOIN moyen_paiement mp ON c.id_moyen_paiement = mp.id_moyen_paiement
                    LEFT JOIN statut s ON c.id_statut = s.id_statut
                    LEFT JOIN avoir av ON c.id_avoir = av.id_avoir
                    LEFT JOIN client cl3 ON av.id_client = cl3.id_client
                    LEFT JOIN profil pro3 ON cl3.id_profil = pro3.id_profil
                    LEFT JOIN civilite civ3 ON cl3.id_civilite = civ3.id_civilite
                    WHERE 
                        cl.id_client = '.intval($this->getId());
        
            $ps = Db::Execute($sql, true);
            $tabRetour = array(); 
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                $client = new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], 
                                    $p['cltelephone'], $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], 
                                    $p['cldate_modif'], $p['clvalide']);
                $adresse = new Adresse($p['aid_adresse'], $client, $p['aadr1'], $p['aadr2'], $p['aville'], $p['acp'], $p['apays'], 
                                        $p['asupprime'], $p['avalide']);
                $profil = new Profil($p['pro2id_profil'], $p['pro2libelle'], $p['pro2valide']);
                $civilite = new Civilite($p['civ2id_civilite'], $p['civ2libelle'], $p['civ2diminutif'], $p['civ2valide']);
                $client = new Client($p['cl2id_client'], $civilite, $profil, $p['cl2login'], $p['cl2mdp'], $p['cl2nom'], $p['cl2prenom'], 
                                    $p['cl2telephone'], $p['cl2mobile'], $p['cl2email'], $p['cl2date_naissance'], $p['cl2date_ajout'], 
                                    $p['cl2date_modif'], $p['cl2valide']);
                $panier = new Panier($p['pid_panier'], $client, $p['pdate_ajout'], $p['pdate_modif']);
                $tva = new Tva($p['tvid_tva'], $p['tvtaux']);
                $transporteur = new Transporteur($p['tid_transporteur'], $p['tlibelle'], $p['ttel'], $p['temail'], $p['tfranco_port'], $p['tfrais_port'], $tva,
                                                                                $p['tdate_ajout'], $p['tdate_modif'], $p['tvalide']);
                $moyen_paiement = new MoyenPaiement($p['mpid_moyen_paiement'], $p['mplibelle'], $p['mpvalide']);
                $statut = new Statut($p['sid_statut'], $p['slibelle'], $p['sfin'], $p['svalide']);
                $profil = new Profil($p['pro3id_profil'], $p['pro3libelle'], $p['pro3valide']);
                $civilite = new Civilite($p['civ3id_civilite'], $p['civ3libelle'], $p['civ3diminutif'], $p['civ3valide']);
                $client = new Client($p['cl3id_client'], $civilite, $profil, $p['cl3login'], $p['cl3mdp'], $p['cl3nom'], $p['cl3prenom'], 
                                    $p['cl3telephone'], $p['cl3mobile'], $p['cl3email'], $p['cl3date_naissance'], $p['cl3date_ajout'], 
                                    $p['cl3date_modif'], $p['cl3valide']);
                $avoir = new Avoir($p['avid_avoir'], $client, $p['avid_commande'], $p['avmontant'], $p['avvalide']);
                $tabRetour[] = new Commande($p['cid_commande'], $adresse, $panier, $transporteur, $moyen_paiement, $statut, $avoir, 
                                            $p['cprix_total_ht'], $p['cprix_total_ttc'], $p['cfrais_transporteur'], $p['cnumero_livraison'], 
                                            $p['cdate_livraison'], $p['cdate_ajout'], $p['cdate_modif']);
            }    
            
            return $tabRetour;
        }

                
        //Récupère les adresses d'un client
        //Paramètre : Aucun
        //Retourne une collection de Adresse
        public function getAdresses(){
            $sql =  'SELECT
                        a.id_adresse as aid_adresse, a.adr1 as aadr1, a.adr2 as aadr2, a.ville as aville, a.cp as acp, a.pays as apays, a.supprime as asupprime, 
                        a.valide as avalide
                    FROM adresse a
                    WHERE 
                        a.id_client  = '.intval($this->getId());
            $ps = Db::Execute($sql, true);
            $tabRetour = array();
            foreach($ps as $p){
                $tabRetour[] = new Adresse($p['aid_adresse'], $this, $p['aadr1'], $p['aadr2'], $p['aville'], $p['acp'], $p['apays'], 
                                            $p['asupprime'], $p['avalide']);   
            }

            return $tabRetour;
        }

        //Récupères les avois de ce Client 
        //Paramètre : OPTIONNEL (Booléen)valide
        //Retourne collection de Avoir
        public function getAvoirs($valide = true) {
            $sql =  'SELECT
                        av.id_avoir as avid_avoir, av.id_commande as avid_commande, av.montant as avmontant, avvalide as avvalide,
                    FROM avoir av
                    WHERE
                       av.id_client = '.intval($this->getId());
            if($valide) {
                $sql .= ' AND av.valide = 1';
            }
            $ps = Db::Execute($sql, true);
            $tabRetour = array();
            foreach($ps as $p) {
                $tabRetour[] = new Avoir($p['avid_avoir'], $this, $p['avid_commande'], $p['avmontant'], $p['avvalide']);
            }
            return $tabRetour;
        }
        
        //Regarde si l'utilisateur à accès au module passé en paramètre
        //Paramètre : (Module)module
        //Retourne (Access)
        public function autorisation($module) {
            $sql =  'SELECT
                        a.droit as adroit
                    FROM acces a
                    WHERE
                        a.id_profil = '.intval($this->profil->getId()).'
                    AND a.id_module = '.intval($module->getId());
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                return new Access($module, $this->profil, $p['adroit']);
            }
            
            return null;
        }
        
        //Initialise le Panier pour ce Client
        //Paramètre : aucun
        //Retourne (Panier)
        public function initPanier() {
            $sql =  'SELECT
                        p.id_panier as pid_panier, p.date_ajout as pdate_ajout, p.date_modif as pdate_modif
                    FROM panier p
                    WHERE
                        p.id_client = '.intval($this->getId()).'
                    AND p.id_panier NOT IN  (SELECT  
                                                c.id_panier
                                            FROM commande c
                                            WHERE
                                                c.id_statut <> 5)';
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                return new Panier($p['pid_panier'], $this, $p['pdate_ajout'], $p['pdate_modif']);
            }
            
            $panier = new Panier(0, $this, new DateTime(), new DateTime());
            $panier->add();
            
            return $panier;
        }
        
        //Récupère le total des achats de ce client sur le site
        //Paramètre : aucun
        //Retourne (Double)
        public function getTotalAchat() {
            $sql =  'SELECT
                        SUM(c.prix_total_ttc) as total
                    FROM commande c
                    WHERE
                        c.id_adresse IN (SELECT DISTINCT 
                                            a.id_adresse
                                        FROM adresse a
                                        WHERE
                                            a.id_client = '.intval($this->getId()).'
                                        )';
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                return $p['total'];
            }
            return 0.00;
        }
        
        //Met le client avec le Profil VIP
        //Paramètre : aucun
        //Retourne (Client)
        public function setVIP() {
            $profil = Profil::rechercheProfil(5);
            $this->setProfil($profil);
            return $this->update();
        }
        
        //Regarde si le login est déja utilisé
        //Paramètre : (String)login
        //Retourne (Booléen)
        public static function estDejaUtilise($login) {
            $sql =  'SELECT
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide
                    FROM client cl
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    WHERE
                        cl.login = "'.Db::insertion($login).'"';
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                return true;
            }
            
            return false;
        }
        
        //Récupère un Client en fonction de son email
        //Paramètre : (String)email
        //Retourne (Client)
        public static function getClientByEmail($email) {
            $sql =  'SELECT
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide
                    FROM client cl
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    WHERE
                        cl.email = "'.Db::insertion($email).'"';
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                return new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], 
                                $p['cltelephone'], $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], 
                                $p['cldate_modif'], $p['clvalide']);
            }

            return null;
        }
        
        //Récupère tout les clients enregistrés
        //Paramètre : aucun
        //Retourne collection de Client
        public static function getClients() {
            $sql =  'SELECT
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide
                    FROM client cl
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite';
            $ps = Db::Execute($sql, true);
            $tabRetour = array();
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                $tabRetour[] = new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], 
                                        $p['cltelephone'], $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], 
                                        $p['cldate_modif'], $p['clvalide']);
            }
            return $tabRetour;
        }
        
        //Vérifie si le couple login/mdp est valide
        //Paramètre : (String)login, (String)mdp
        //Retourne (Client)
        public static function connect($login, $mdp) {
            $sql =  'SELECT
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide
                    FROM client cl
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    WHERE
                        cl.valide = 1
                    AND cl.login = "'.Db::insertion($login).'"
                    AND cl.mdp = "'.Db::insertion(Tools::encrypt($mdp)).'"';
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                return new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], 
                                $p['cltelephone'], $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], 
                                $p['cldate_modif'], $p['clvalide']);
            }

            return null; 
        }
        
        //Crée un objet Client grâce à son ID
        //Paramètre : (int)id
        //Retourne (Client)
        public static function rechercheClient($id) {
            $sql =  'SELECT
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide
                    FROM client cl
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    WHERE
                        cl.id_client = '.intval($id);
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                return new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], 
                                $p['cltelephone'], $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], 
                                $p['cldate_modif'], $p['clvalide']);
            }

            return null;
        }
                
                
		
            //GETTERS ET SETTERS
            public function getLogin() {return $this->login;}

            public function setLogin($temp) {$this->login = $temp;}
    }
?>